/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import com.mysql.jdbc.PreparedStatement;
import conectaBD.CriaConexao;
import java.sql.*;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import negocios.Livro;

/**
 *
 * @author grupo
 */
public class LivroDAO {

    private Connection conexao;

    public LivroDAO() throws SQLException {
        this.conexao = (Connection) CriaConexao.getConexao();

    }

    public void adicionar(Livro a) {
        try {
            String sql = "insert into livro (num,data,autor,titulo,ex,volume,local,editora,genero,tipo,"
                    + "ano_publicacao,cdd,cdu,obs,id_livro) values (?,?,?, ?, ?, ?, ?, ?, ?, ?, ? , ? , ? , ?,?)";
            PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
            stmt.setInt(1, a.getNum());
            stmt.setString(2, a.getData());
            stmt.setString(3, a.getAutor());
            stmt.setString(4, a.getTitulo());
            stmt.setInt(5, a.getEx());
            stmt.setInt(6, a.getVolume());
            stmt.setString(7, a.getLocal());
            stmt.setString(8, a.getEditora());
            stmt.setString(9, a.getGenero());
            stmt.setString(10, a.getTipo());
            stmt.setString(11, a.getAno_publicacao());
            stmt.setLong(12, a.getCDD());
            stmt.setLong(13, a.getCDU());
            stmt.setString(14, a.getObs());
            stmt.setLong(15, a.getId());
            stmt.execute();
            stmt.close();
        } catch (SQLException ex) {
            System.out.println("Erro" + ex.getMessage());
        }
    }

    public void altera(Livro a) throws SQLException {
        String sql = "update Livro set data=?,autor=?,titulo=?,ex=?,volume=?,local=?,editora=?,genero=?,tipo=?,ano_publicacao=?,cdd=?,cdu=?,obs=?,num=? where id_livro=?";
        PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
        stmt.setLong(15, a.getId());
        stmt.setString(1, a.getData());
        stmt.setString(2, a.getAutor());
        stmt.setString(3, a.getTitulo());
        stmt.setInt(4, a.getEx());
        stmt.setInt(5, a.getVolume());
        stmt.setString(6, a.getLocal());
        stmt.setString(7, a.getEditora());
        stmt.setString(8, a.getGenero());
        stmt.setString(9, a.getTipo());
        stmt.setString(10, a.getAno_publicacao());
        stmt.setLong(11, a.getCDD());
        stmt.setLong(12, a.getCDU());
        stmt.setString(13, a.getObs());
        stmt.setInt(14, a.getNum());
        stmt.execute();
        stmt.close();
    }

    public void exclui(Livro a) throws SQLException {

        String sql_e = "delete from Livro where id_livro=?";

        PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql_e);
        stmt.setLong(1, a.getId());
        stmt.execute();
        stmt.close();

    }

    public List<Livro> getList() throws SQLException {
        //Buscando Aluno
        String sql = "select * from Livro ";

        PreparedStatement consulta_v = (PreparedStatement) conexao.prepareStatement(sql);

        ResultSet rs = consulta_v.executeQuery();

        List<Livro> minhaLista = new ArrayList<Livro>();

        while (rs.next()) {
            Livro a1 = new Livro();
            a1.setNum(rs.getInt("num"));
            a1.setData(rs.getString("data"));
            a1.setTitulo(rs.getString("titulo"));
            a1.setAutor(rs.getString("autor"));
            a1.setEx(rs.getInt("ex"));
            a1.setVolume(rs.getInt("volume"));
            a1.setLocal(rs.getString("local"));
            a1.setEditora(rs.getString("editora"));
            a1.setGenero(rs.getString("genero"));
            a1.setTipo(rs.getString("tipo"));
            a1.setAno_publicacao(rs.getString("ano_publicacao"));
            a1.setCDD(rs.getLong("cdd"));
            a1.setCDU(rs.getLong("cdu"));
            a1.setObs(rs.getString("obs"));
            minhaLista.add(a1);
        }
        rs.close();
        consulta_v.close();
        return minhaLista;
    }

    public List<Livro> Buscar(String titulo) throws SQLException {

        String sql = "select * from Livro where titulo like ? order by titulo ";
        PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql);
        stmt.setString(1, titulo);
        ResultSet rs = stmt.executeQuery();
        List<Livro> minhaLista = new ArrayList<Livro>();
        while (rs.next()) {
            Livro a1 = new Livro();
            a1.setId(rs.getLong("id_livro"));
            a1.setNum(rs.getInt("num"));
            a1.setData(rs.getString("data"));
            a1.setTitulo(rs.getString("titulo"));
            a1.setAutor(rs.getString("autor"));
            a1.setEx(rs.getInt("ex"));
            a1.setVolume(rs.getInt("volume"));
            a1.setLocal(rs.getString("local"));
            a1.setEditora(rs.getString("editora"));
            a1.setGenero(rs.getString("genero"));
            a1.setTipo(rs.getString("tipo"));
            a1.setAno_publicacao(rs.getString("ano_publicacao"));
            a1.setCDD(rs.getLong("cdd"));
            a1.setCDU(rs.getLong("cdu"));
            a1.setObs(rs.getString("obs"));
            minhaLista.add(a1);
        }
        rs.close();
        stmt.close();
        return minhaLista;

    }
}